DROP TABLE Employee;
DROP TABLE Project;
DROP TABLE VacationUsed;
DROP TABLE SickUsed;
DROP TABLE Timesheet;
DROP TABLE Assignment;


CREATE TABLE Employee(
EID Char(4) PRIMARY KEY,
FirstName Char(15),
LastName Char(15),
Address char(20),
City Char(15),
ZIP Char(5),
Phone Char(10),
Title Char(15),
Salary Numeric(9,2),
Vac Numeric(4,1),
Sick Numeric(4,1) );

CREATE TABLE Project(
PID Char(4) PRIMARY KEY,
PName Char(20),
Type Char(1),
ActualHrs Numeric(4,1),
ExpectedHrs Numeric(4,1),
ManagerID Char(4),
CONSTRAINT Proj_Emp_FK FOREIGN KEY (ManagerID) REFERENCES Employee(EID) );

CREATE TABLE VacationUsed(
EID Char(4),
BeginDate Date,
EndDate Date,
Approval Char(4),
CONSTRAINT VUsed_PK PRIMARY KEY (EID, BeginDate),
CONSTRAINT Vac_Emp_FK FOREIGN KEY (Approval) REFERENCES Employee(EID) );

CREATE TABLE SickUsed(
EID Char(4),
BeginDate Date,
EndDate Date,
Approval Char(4),
CONSTRAINT SUsed_PK PRIMARY KEY (EID, BeginDate),
CONSTRAINT Sick_Emp_FK FOREIGN KEY (Approval) REFERENCES Employee(EID) );

CREATE TABLE Timesheet(
EID Char(4),
PID Char(4),
DateRecorded Date,
Hours Numeric(2,1),
CONSTRAINT Time_PK PRIMARY KEY (EID, PID, DateRecorded),
CONSTRAINT TSheet_Emp_FK FOREIGN KEY (EID) REFERENCES Employee(EID),
CONSTRAINT TSheet_Proj_FK FOREIGN KEY (PID) REFERENCES Project(PID) );

CREATE TABLE Assignment(
EID Char(4),
PID Char(4),
HrsWorked Numeric(4,1),
Evaluation Char(40),
CONSTRAINT Time_PK PRIMARY KEY (EID, PID),
CONSTRAINT Assign_Emp_FK FOREIGN KEY (EID) REFERENCES Employee(EID),
CONSTRAINT Assign_Proj_FK FOREIGN KEY (PID) REFERENCES Project(PID) );

INSERT INTO Employee VALUES ("2698", "Joaquin", "Wokout", "12 Maple St.", "Atlanta", "30311", "4047581267", "Programmer", 2200, 3, 8);
INSERT INTO Employee VALUES ("9493", "Max", "Stout", "28 Alma St.", "Smyrna", "30364", "4047484847", "Programmer", 2200, 5, 8);
INSERT INTO Employee VALUES ("8599", "Selma", "Junkov", "373 Ave A", "Smyrna", "30364", "4048379383", "Consultant", 2500, 5, 7);
INSERT INTO Employee VALUES ("0423", "Euripides", "Imendum", "3888 6th St.", "Smyrna", "30364", "4048372552", "Consultant", 2500, 4, 5);
INSERT INTO Employee VALUES ("3993", "Jim", "Nasium", "149 Oakside Dr.", "Atlanta", "30311", "4047587399", "Programmer", 2300, 3, 7);
INSERT INTO Employee VALUES ("8462", "Pekup", "Andropov", "2333 Briarcrest Dr.", "Atlanta", "30312", "4047666752", "Programmer", 2350, 7, 8);
INSERT INTO Employee VALUES ("2010", "Marge N.", "Overra", "Windgate Dr.", "Smyrna", "30364", "4048377288", "Manager", 3500, 7, 7);
INSERT INTO Employee VALUES ("7324", "C.F.", "Icare", "131 Rhodes Rd.", "Smyrna", "30367", "4049770979", "Manager", 4200, 3, 8);

INSERT INTO Project VALUES ("6301", "Marketing report", "E", 12, 120, "8462");
INSERT INTO Project VALUES ("2376", "New billing system", "E", 80, 160, "8462");
INSERT INTO Project VALUES ("2876", "Common stock report", "C", 12, 8, "3993");
INSERT INTO Project VALUES ("2671", "Bad debt report", "D", 40, 20, "0423");
INSERT INTO Project VALUES ("2651", "Manpower report bug", "C", 8, 16, "9493");

INSERT INTO VacationUsed VALUES ("2698", "2017-07-01", "2017-07-05", "2010");
INSERT INTO VacationUsed VALUES ("0423", "2017-08-10", "2017-08-10", "2010");
INSERT INTO VacationUsed VALUES ("3993", "2017-09-04", "2017-09-07", "2010");
INSERT INTO VacationUsed VALUES ("9493", "2017-09-04", "2017-09-04", "7324");
INSERT INTO VacationUsed VALUES ("3993", "2017-11-20", "2017-11-21", "7324");

INSERT INTO SickUsed VALUES ("2698", "2017-03-02", "2017-03-02", "2010");
INSERT INTO SickUsed VALUES ("3993", "2017-05-10", "2017-05-14", "2010");

INSERT INTO Timesheet VALUES ("2698", "6301", "2017-10-02", 3.5);
INSERT INTO Timesheet VALUES ("2698", "2376", "2017-10-02", 5.5);
INSERT INTO Timesheet VALUES ("0423", "2671", "2017-10-02", 8.0);
INSERT INTO Timesheet VALUES ("9493", "2376", "2017-10-02", 8.0);
INSERT INTO Timesheet VALUES ("8599", "2876", "2017-10-02", 5.0);
INSERT INTO Timesheet VALUES ("8599", "2671", "2017-10-02", 3.0);
INSERT INTO Timesheet VALUES ("8462", "2376", "2017-10-02", 3.5);
INSERT INTO Timesheet VALUES ("8462", "6301", "2017-10-02", 4.5);
INSERT INTO Timesheet VALUES ("2698", "6301", "2017-10-03", 7.5);
INSERT INTO Timesheet VALUES ("2698", "2376", "2017-10-03", 0.5);
INSERT INTO Timesheet VALUES ("0423", "2671", "2017-10-03", 8.0);
INSERT INTO Timesheet VALUES ("9493", "2376", "2017-10-03", 8.0);
INSERT INTO Timesheet VALUES ("8599", "2876", "2017-10-03", 4.0);
INSERT INTO Timesheet VALUES ("8599", "2671", "2017-10-03", 4.0);

INSERT INTO Assignment VALUES ("2698", "6301", 11.0, "On schedule");
INSERT INTO Assignment VALUES ("2698", "2376", 6.0, "On schedule");
INSERT INTO Assignment VALUES ("0423", "2671", 16.0, "Slipping, needs analysis");
INSERT INTO Assignment VALUES ("9493", "2376", 16.0, "Late start but OK now");
INSERT INTO Assignment VALUES ("8599", "2876", 9.0, "Analysis problems");
INSERT INTO Assignment VALUES ("8599", "2671", 7.0, "Appears on schedule needs, formal review");
INSERT INTO Assignment VALUES ("8462", "2376", 3.5, "OK");
INSERT INTO Assignment (EID, PID, HrsWorked) VALUES ("8462", "6301", 4.5);
INSERT INTO Assignment (EID, PID) VALUES ("3993", "2651");